﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class DriverDA : IDriverDA
    {
        private string ADDRESS = "@ADDRESS";
        private string AGE = "@AGE";
        private string CARRIER_ID = "@CARRIER_ID";
        private string CITY = "@CITY";
        private string CREATED_BY = "@CREATED_BY";
        private string DRIVER_CARD_NO = "@DRIVER_CARD_NO";
        private string DRIVER_CARD_TYPE = "@DRIVER_CARD_TYPE";
        private string DRIVER_ID = "@DRIVER_ID";
        private string DRIVER_NAME = "@DRIVER_NAME";
        private string FAX = "@FAX";
        private string ID_CARD = "@ID_CARD";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string PRIORITY = "@PRIORITY";
        private string PROVINCE = "@PROVINCE";
        private string REMARK = "@REMARK";
        private string SEX = "@SEX";
        private string SQL_CHECK_DRIVER_ID_UNIQUE = " SELECT COUNT(1) FROM @_@DRIVER WHERE DRIVER_ID = @DRIVER_ID ";
        private string SQL_DELETE_DRIVER = " DELETE FROM @_@DRIVER WHERE DRIVER_ID = @DRIVER_ID ";
        private string SQL_INSERT_DRIVER = " INSERT INTO @_@DRIVER ( WH_ID, DRIVER_ID, CARRIER_ID, DRIVER_NAME, SEX, AGE, ID_CARD, DRIVER_CARD_NO, DRIVER_CARD_TYPE, PRIORITY, STATUS, TELEPHONE, FAX, PROVINCE, CITY, ZIP, ADDRESS, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @DRIVER_ID, @CARRIER_ID, @DRIVER_NAME, @SEX, @AGE, @ID_CARD, @DRIVER_CARD_NO, @DRIVER_CARD_TYPE, @PRIORITY, @STATUS, @TELEPHONE, @FAX, @PROVINCE, @CITY, @ZIP, @ADDRESS, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_DRIVER = "SELECT  DR.ROWID, DR.WH_ID, DR.DRIVER_ID, DR.CARRIER_ID, DR.DRIVER_NAME, DR.SEX, DR.AGE, DR.ID_CARD, DR.DRIVER_CARD_NO, DR.DRIVER_CARD_TYPE, DR.PRIORITY,   DR.STATUS  as STATUS, T.[TEXT] AS DR_STATUS,  DR.TELEPHONE, DR.FAX, DR.PROVINCE, DR.CITY, DR.ZIP, DR.ADDRESS, DR.REMARK, DR.IS_ACTIVE, DR.TS, DR.CREATED_BY, DR.CREATED_DATE, DR.UPDATED_BY, DR.UPDATED_DATE    FROM  @_@DRIVER as DR  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = DR.STATUS AND SCD.SYS_CODE_TYPE = 'DriverStatus'   LEFT JOIN sysadm.TRANSLATION T ON 'DriverStatus.'+ DR.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE ";
        private string SQL_UPDATE_DRIVER = " UPDATE @_@DRIVER SET WH_ID = @WH_ID, CARRIER_ID = @CARRIER_ID, DRIVER_NAME = @DRIVER_NAME, SEX = @SEX, AGE = @AGE, ID_CARD = @ID_CARD, DRIVER_CARD_NO = @DRIVER_CARD_NO, DRIVER_CARD_TYPE = @DRIVER_CARD_TYPE, PRIORITY = @PRIORITY, STATUS = @STATUS, TELEPHONE = @TELEPHONE, FAX = @FAX, PROVINCE = @PROVINCE, CITY = @CITY, ZIP = @ZIP, ADDRESS = @ADDRESS, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE DRIVER_ID = @DRIVER_ID  ";
        private string STATUS = "@STATUS";
        private string TELEPHONE = "@TELEPHONE";
        private string TS = "@TS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";
        private string ZIP = "@ZIP";

        public bool CheckDriverIDUnique(string driverID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DRIVER_ID", driverID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_DRIVER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckDriverIDUnique(DataBase dataBase, DbTransaction tran, string driverID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DRIVER_ID", driverID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_DRIVER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteDriver(List<string> driverID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < driverID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DRIVER_ID", driverID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DRIVER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteDriver(DataBase dataBase, DbTransaction tran, List<string> driverID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < driverID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DRIVER_ID", driverID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DRIVER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllDriver(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DRIVER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllDriver(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DRIVER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public DriverInfo GetDriverByID(string driverID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DRIVER.Replace("@_@", whLoginID) + " WHERE DRIVER_ID = @DRIVER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DRIVER_ID", driverID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            DriverInfo driverInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    driverInfo = new DriverInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DRIVER_ID"].ToString(), reader["CARRIER_ID"].ToString(), reader["DRIVER_NAME"].ToString(), reader["SEX"].ToString(), (reader["AGE"].ToString() != "") ? int.Parse(reader["AGE"].ToString()) : 0, reader["ID_CARD"].ToString(), reader["DRIVER_CARD_NO"].ToString(), reader["DRIVER_CARD_TYPE"].ToString(), reader["PRIORITY"].ToString(), reader["STATUS"].ToString(), reader["TELEPHONE"].ToString(), reader["FAX"].ToString(), reader["PROVINCE"].ToString(), reader["CITY"].ToString(), reader["ZIP"].ToString(), reader["ADDRESS"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return driverInfo;
        }

        public DriverInfo GetDriverByID(DataBase dataBase, DbTransaction tran, string driverID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DRIVER.Replace("@_@", whLoginID) + " WHERE DRIVER_ID = @DRIVER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DRIVER_ID", driverID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            DriverInfo driverInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                driverInfo = new DriverInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DRIVER_ID"].ToString(), reader["CARRIER_ID"].ToString(), reader["DRIVER_NAME"].ToString(), reader["SEX"].ToString(), (reader["AGE"].ToString() != "") ? int.Parse(reader["AGE"].ToString()) : 0, reader["ID_CARD"].ToString(), reader["DRIVER_CARD_NO"].ToString(), reader["DRIVER_CARD_TYPE"].ToString(), reader["PRIORITY"].ToString(), reader["STATUS"].ToString(), reader["TELEPHONE"].ToString(), reader["FAX"].ToString(), reader["PROVINCE"].ToString(), reader["CITY"].ToString(), reader["ZIP"].ToString(), reader["ADDRESS"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return driverInfo;
        }

        public DataSet GetDriverByQueryList(List<string> sqlWhere, DriverQueryEntity driverQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_DRIVER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!driverQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, driverQuery.CurrentPage, driverQuery.PageSize, driverQuery.SortField, driverQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetDriverByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, DriverQueryEntity driverQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_DRIVER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (driverQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, driverQuery.CurrentPage, driverQuery.PageSize, driverQuery.SortField, driverQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertDriver(DriverInfo driverInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    driverInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Driver_Parameters(driverInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_DRIVER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertDriver(DataBase dataBase, DbTransaction tran, DriverInfo driverInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    driverInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Driver_Parameters(driverInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_DRIVER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Driver_Parameters(DriverInfo driverInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DRIVER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DRIVER_NAME, SqlDbType.VarChar, 20), new SqlParameter(this.SEX, SqlDbType.VarChar, 1), new SqlParameter(this.AGE, SqlDbType.Int, 4), new SqlParameter(this.ID_CARD, SqlDbType.VarChar, 20), new SqlParameter(this.DRIVER_CARD_NO, SqlDbType.VarChar, 20), new SqlParameter(this.DRIVER_CARD_TYPE, SqlDbType.VarChar, 1), new SqlParameter(this.PRIORITY, SqlDbType.VarChar, 1), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.TELEPHONE, SqlDbType.VarChar, 20), new SqlParameter(this.FAX, SqlDbType.VarChar, 20), new SqlParameter(this.PROVINCE, SqlDbType.VarChar, 20), new SqlParameter(this.CITY, SqlDbType.VarChar, 20), new SqlParameter(this.ZIP, SqlDbType.VarChar, 7), 
                new SqlParameter(this.ADDRESS, SqlDbType.VarChar, 100), new SqlParameter(this.REMARK, SqlDbType.VarChar, 100), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(driverInfo.WhID))
            {
                paramArray[0].Value = driverInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = driverInfo.DriverID;
            paramArray[2].Value = driverInfo.CarrierID;
            paramArray[3].Value = driverInfo.DriverName;
            if (!string.IsNullOrEmpty(driverInfo.Sex))
            {
                paramArray[4].Value = driverInfo.Sex;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            paramArray[5].Value = driverInfo.Age;
            if (!string.IsNullOrEmpty(driverInfo.IdCard))
            {
                paramArray[6].Value = driverInfo.IdCard;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            paramArray[7].Value = driverInfo.DriverCardNo;
            if (!string.IsNullOrEmpty(driverInfo.DriverCardType))
            {
                paramArray[8].Value = driverInfo.DriverCardType;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(driverInfo.Priority))
            {
                paramArray[9].Value = driverInfo.Priority;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(driverInfo.Status))
            {
                paramArray[10].Value = driverInfo.Status;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(driverInfo.Telephone))
            {
                paramArray[11].Value = driverInfo.Telephone;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(driverInfo.Fax))
            {
                paramArray[12].Value = driverInfo.Fax;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(driverInfo.Province))
            {
                paramArray[13].Value = driverInfo.Province;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(driverInfo.City))
            {
                paramArray[14].Value = driverInfo.City;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(driverInfo.Zip))
            {
                paramArray[15].Value = driverInfo.Zip;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(driverInfo.Address))
            {
                paramArray[0x10].Value = driverInfo.Address;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(driverInfo.Remark))
            {
                paramArray[0x11].Value = driverInfo.Remark;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            paramArray[0x12].Value = driverInfo.IsActive;
            paramArray[0x13].Value = driverInfo.CreatedBy;
            paramArray[20].Value = driverInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateDriver(DriverInfo driverInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    driverInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Driver_Parameters(driverInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_DRIVER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateDriver(DataBase dataBase, DbTransaction tran, DriverInfo driverInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    driverInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Driver_Parameters(driverInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_DRIVER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

